/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package fullalbum.dao;

import fullalbum.TransactionDAO;
import fullalbum.bean.UserAlbum;
import fullalbum.vo.UserAlbumDetailVO;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author leoomoreira
 */
public class UserAlbumDAO {

    public List<UserAlbumDetailVO> getCompleteQueryUserAlbum(String userName, String albumName) {
        String sql = "select ua.user_id, " +
                "u.name as user_name, " +
                "ua.album_id, " +
                "a.name as album_name, " +
                "COALESCE((select count(*) as total_figure " +
                    "from user_album_figure uaf " +
                    "where uaf.album_id = ua.album_id " +
                    "and uaf.user_id = u.id " +
                    "group by uaf.album_id), 0) as figure_owned, " +
                "(select count(*) from figure f where f.album_id = ua.album_id) as total_album_figure " +
                "from user_album ua, \"user\" u, album a " +
                "where a.id = ua.album_id " +
                "and upper(a.name) like upper('" + albumName + "') " +
                "and u.id = ua.user_id " +
                "and upper(u.name) like upper('" + userName + "') " +
                "order by figure_owned desc, user_id asc";
        List<UserAlbumDetailVO> result = new ArrayList<UserAlbumDetailVO>();
        TransactionDAO dao = new TransactionDAO();
        ResultSet resultSet = dao.executeQuery(sql);
        try {
            while (resultSet != null && resultSet.next()) {
                UserAlbumDetailVO advo = new UserAlbumDetailVO();

                Integer queryUserId = Integer.parseInt(resultSet.getString("user_id"));
                Integer queryAlbumId = Integer.parseInt(resultSet.getString("album_id"));
                Integer queryFigureOwned = Integer.parseInt(resultSet.getString("figure_owned"));
                Integer queryTotalAlbumFigure = Integer.parseInt(resultSet.getString("total_album_figure"));
                String queryAlbumName = resultSet.getString("album_name");
                String queryUserName = resultSet.getString("user_name");

                advo.setAlbumId(queryAlbumId);
                advo.setAlbumName(queryAlbumName);
                advo.setFigureOwned(queryFigureOwned);
                advo.setTotalAlbumFigure(queryTotalAlbumFigure);
                advo.setUserId(queryUserId);
                advo.setUserName(queryUserName);

                result.add(advo);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserAlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return result;
    }

    public List<UserAlbum> getAll(Integer albumId) {
        String sql = "select ua.user_id, ua.album_id " +
                "from user_album ua where ua.album_id = " + albumId;
        List<UserAlbum> userAlbumList = new ArrayList<UserAlbum>();
        TransactionDAO dao = new TransactionDAO();
        AlbumDAO albumDAO = new AlbumDAO();
        UserDAO userDAO = new UserDAO();
        ResultSet resultSetUsersAlbum = dao.executeQuery(sql);
        try {
            while (resultSetUsersAlbum != null && resultSetUsersAlbum.next()) {
                UserAlbum userAlbum = new UserAlbum();

                String queryUserId = resultSetUsersAlbum.getString("user_id");
                String queryAlbumId = resultSetUsersAlbum.getString("album_id");

                userAlbum.setUser(userDAO.getById(Integer.parseInt(queryUserId)));
                userAlbum.setAlbum(albumDAO.getById(Integer.parseInt(queryAlbumId)));

                userAlbumList.add(userAlbum);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserAlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return userAlbumList;
    }

    public List<UserAlbum> getAllExceptUserId(Integer userId) {
        String sqlUsersAlbum = "select ua.user_id, ua.album_id " +
                "from user_album ua where ua.user_id <> " + userId;

        List<UserAlbum> userAlbumList = new ArrayList<UserAlbum>();
        TransactionDAO dao = new TransactionDAO();
        AlbumDAO albumDAO = new AlbumDAO();
        UserDAO userDAO = new UserDAO();

        ResultSet resultSetUsersAlbum = dao.executeQuery(sqlUsersAlbum);
        try {
            while (resultSetUsersAlbum != null && resultSetUsersAlbum.next()) {
                UserAlbum userAlbum = new UserAlbum();

                String queryUserId = resultSetUsersAlbum.getString("user_id");
                String queryAlbumId = resultSetUsersAlbum.getString("album_id");

                userAlbum.setUser(userDAO.getById(Integer.parseInt(queryUserId)));
                userAlbum.setAlbum(albumDAO.getById(Integer.parseInt(queryAlbumId)));

                userAlbumList.add(userAlbum);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserAlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return userAlbumList;
    }


    public List<UserAlbum> getByUserId(Integer userId) {
        String sqlUsersAlbum = "select ua.user_id, ua.album_id " +
                "from user_album ua where ua.user_id = " + userId;

        List<UserAlbum> userAlbumList = new ArrayList<UserAlbum>();
        TransactionDAO dao = new TransactionDAO();
        AlbumDAO albumDAO = new AlbumDAO();
        UserDAO userDAO = new UserDAO();

        ResultSet resultSetUsersAlbum = dao.executeQuery(sqlUsersAlbum);
        try {
            while (resultSetUsersAlbum != null && resultSetUsersAlbum.next()) {
                UserAlbum userAlbum = new UserAlbum();

                String queryUserId = resultSetUsersAlbum.getString("user_id");
                String queryAlbumId = resultSetUsersAlbum.getString("album_id");

                userAlbum.setUser(userDAO.getById(Integer.parseInt(queryUserId)));
                userAlbum.setAlbum(albumDAO.getById(Integer.parseInt(queryAlbumId)));

                userAlbumList.add(userAlbum);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserAlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return userAlbumList;
    }

    public List<UserAlbum> getByAlbumId(Integer albumId) {
        String sqlUsersAlbum = "select ua.user_id, ua.album_id " +
                "from user_album ua where ua.album_id = " + albumId;

        List<UserAlbum> userAlbumList = new ArrayList<UserAlbum>();
        TransactionDAO dao = new TransactionDAO();
        AlbumDAO albumDAO = new AlbumDAO();
        UserDAO userDAO = new UserDAO();

        ResultSet resultSetUsersAlbum = dao.executeQuery(sqlUsersAlbum);
        try {
            while (resultSetUsersAlbum != null && resultSetUsersAlbum.next()) {
                UserAlbum userAlbum = new UserAlbum();

                String queryUserId = resultSetUsersAlbum.getString("user_id");
                String queryAlbumId = resultSetUsersAlbum.getString("album_id");

                userAlbum.setUser(userDAO.getById(Integer.parseInt(queryUserId)));
                userAlbum.setAlbum(albumDAO.getById(Integer.parseInt(queryAlbumId)));

                userAlbumList.add(userAlbum);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserAlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        dao.commit();
        dao.close();
        return userAlbumList;
    }

    public UserAlbum getByUserIdAndAlbumId(Integer userId, Integer albumId) {
        String sqlUserAlbum = "select ua.user_id, ua.album_id " +
                "from user_album ua where ua.user_id = " + userId + " and ua.album_id = " + albumId;

        UserAlbum userAlbum = new UserAlbum();
        TransactionDAO dao = new TransactionDAO();
        AlbumDAO albumDAO = new AlbumDAO();
        UserDAO userDAO = new UserDAO();

        ResultSet resultSetUserAlbum = dao.executeQuery(sqlUserAlbum);
        try {
            while (resultSetUserAlbum != null && resultSetUserAlbum.next()) {
                String queryUserId = resultSetUserAlbum.getString("user_id");
                String queryAlbumId = resultSetUserAlbum.getString("album_id");

                userAlbum.setUser(userDAO.getById(Integer.parseInt(queryUserId)));
                userAlbum.setAlbum(albumDAO.getById(Integer.parseInt(queryAlbumId)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserAlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        dao.commit();
        dao.close();
        return userAlbum;
    }

}
